package org.lvhengchang.schoolmanager.dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lvhengchang.schoolmanager.bean.Teacher;
import org.lvhengchang.schoolmanager.util.DataSourceTools;
/**
 * 教师数据持久处理类
 * 
 * @author LvHengchang
 *
 */
public class TeacherDao {
	

	    private DataSource dataSource = DataSourceTools.getDataSource();

	    private final static String SELECT_ALL =
	            "select teacher_id, teacher_name, teacher_birthday, sex, remarks from t_teachers";
	    private final static String SELECT_BY_ID =
	            "select teacher_id, teacher_name, teacher_birthday, sex, remarks from t_teachers where teacher_id = ?";
	    private final static String INSERT_SQL =
	            "insert into t_teachers(teacher_name, teacher_birthday, sex, remarks) value(?,?,?,?)";
	    private final static String UPDATE_SQL =
	            "update t_teachers set teacher_name = ?,teacher_birthday = ?,sex = ?,remarks = ? where teacher_id = ?";
	    private final static String DELETE_SQL =
	            "delete from t_teachers where teacher_id = ?";
	    private final static String LOGIN_SQL =
	            "select id, username, password, is_delete as 'isDelete' from t_admin where username = ? and password = ? and is_delete=0";

	    
	    public List<Teacher> selectAll() throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        List<Teacher> teachers = queryRunner.query(SELECT_ALL,
	                new BeanListHandler<Teacher>(Teacher.class));
	        return teachers;
	    }

	    public Teacher selectById(String teacher_id) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        Teacher teacher = queryRunner.query(SELECT_BY_ID,
	                new BeanHandler<Teacher>(Teacher.class), teacher_id);
	        return teacher;

	    }

	    public int insertTeacher(Teacher teacher) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(INSERT_SQL, teacher.getTeacher_name(), teacher.getTeacher_birthday(), teacher.getSex(), teacher.getRemarks());
	        return result;
	    }

	    public int updateTeacher(Teacher teacher) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(UPDATE_SQL, teacher.getTeacher_name(), teacher.getTeacher_birthday(), teacher.getSex(), teacher.getRemarks(), teacher.getTeacher_id());
	        return result;
	    }

	    public int deleteTeacher(String teacher_id) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(DELETE_SQL, teacher_id);
	        return result;
	    }
	    
	    public Teacher loginTeacher(String username, String password) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        Teacher result = queryRunner.query(LOGIN_SQL, new BeanHandler<Teacher>(Teacher.class), username, password);
	        return result;
	    }
	    
	    
}

